#region Copyright (c) 2006-2012 nHydrate.org, All Rights Reserved
// --------------------------------------------------------------------- *
//                           NHYDRATE.ORG                                *
//              Copyright (c) 2006-2012 All Rights reserved              *
//                                                                       *
//                                                                       *
// This file and its contents are protected by United States and         *
// International copyright laws.  Unauthorized reproduction and/or       *
// distribution of all or any portion of the code contained herein       *
// is strictly prohibited and will result in severe civil and criminal   *
// penalties.  Any violations of this copyright will be prosecuted       *
// to the fullest extent possible under law.                             *
//                                                                       *
// THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *
// TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *
// TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
// CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *
// THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF THE NHYDRATE GROUP *
//                                                                       *
// UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *
// PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *
// SUBSTANTIALLY THE SAME, FUNCTIONALITY AS THIS PRODUCT                 *
//                                                                       *
// THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *
// CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF NHYDRATE,          *
// THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO             *
// INSURE ITS CONFIDENTIALITY.                                           *
//                                                                       *
// THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *
// PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *
// EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *
// THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *
// SOURCE CODE CONTAINED HEREIN.                                         *
//                                                                       *
// THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *
// --------------------------------------------------------------------- *
#endregion
using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using nHydrate.Generator;
using nHydrate.Generator.Models;
using System.Collections;
using nHydrate.Generator.Common.Util;
using nHydrate.Generator.ProjectItemGenerators;

namespace nHydrate.Generator.SQLInstaller.ProjectItemGenerators.DatabaseSchema
{
	public class CreateAuditTriggerTemplate : BaseDbScriptTemplate
	{
		private StringBuilder sb = new StringBuilder();

		#region Constructors
		public CreateAuditTriggerTemplate(ModelRoot model)
			: base(model)
		{
		}
		#endregion

		#region BaseClassTemplate overrides
		public override string FileContent
		{
			get
			{
				GenerateContent();
				return sb.ToString();
			}
		}

		public override string FileName
		{
			get
			{
				return string.Format("CreateSchemaAuditTriggers.sql");
			}
		}
		#endregion

		#region GenerateContent
		private void GenerateContent()
		{
			try
			{
				sb = new StringBuilder();
				sb.AppendLine("--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.");
				sb.AppendLine("--Audit Triggers For Version " + _model.Version);
				sb.AppendLine();
				this.AppendAuditTriggers();
			}
			catch (Exception ex)
			{
				throw;
			}
		}

		private void AppendAuditTriggers()
		{
			sb.AppendLine("--##SECTION BEGIN [AUDIT TRIGGERS]");
			sb.AppendLine();
			foreach (var table in _model.Database.Tables.Where(x => x.Generated && x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
			{
				sb.AppendLine("--DROP ANY AUDIT TRIGGERS FOR [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "]");
				sb.AppendLine("if exists(select * from sysobjects where name = '__TR_" + table.DatabaseName + "__INSERT' AND xtype = 'TR')");
				sb.AppendLine("DROP TRIGGER [" + table.GetSQLSchema() + "].[__TR_" + table.DatabaseName + "__INSERT]");
				sb.AppendLine("GO");
				sb.AppendLine("if exists(select * from sysobjects where name = '__TR_" + table.DatabaseName + "__UPDATE' AND xtype = 'TR')");
				sb.AppendLine("DROP TRIGGER [" + table.GetSQLSchema() + "].[__TR_" + table.DatabaseName + "__UPDATE]");
				sb.AppendLine("GO");
				sb.AppendLine("if exists(select * from sysobjects where name = '__TR_" + table.DatabaseName + "__DELETE' AND xtype = 'TR')");
				sb.AppendLine("DROP TRIGGER [" + table.GetSQLSchema() + "].[__TR_" + table.DatabaseName + "__DELETE]");
				sb.AppendLine("GO");
				sb.AppendLine();

				if (table.AllowAuditTracking)
				{
					var columnList = table.GetColumns();
					var columnText = string.Empty;
					foreach (var column in table.GetColumns())
					{
						if (column.Generated && !(column.DataType == System.Data.SqlDbType.Text || column.DataType == System.Data.SqlDbType.NText || column.DataType == System.Data.SqlDbType.Image))
							columnText += "[" + column.DatabaseName + "],";
					}

					if (table.AllowModifiedAudit)
						columnText += "[" + _model.Database.ModifiedByDatabaseName + "],";
					var columnValues = columnText;
					columnText += "[__insertdate]";
					columnValues += _model.GetSQLDefaultDate();

					sb.AppendLine("--CREATE TRIGGER INSERT FOR [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "]");
					sb.AppendLine("CREATE TRIGGER [" + table.GetSQLSchema() + "].[__TR_" + table.DatabaseName + "__INSERT]");
					sb.AppendLine("ON [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "]");
					sb.AppendLine("FOR INSERT AS");
					sb.AppendLine("INSERT INTO [" + table.GetSQLSchema() + "].[__AUDIT__" + table.DatabaseName + "] ([__action]," + columnText + ")");
					sb.AppendLine("SELECT 1, " + columnValues + " FROM [inserted]");
					sb.AppendLine("GO");
					sb.AppendLine();
					sb.AppendLine("--CREATE TRIGGER UPDATE FOR [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "]");
					sb.AppendLine("CREATE TRIGGER [" + table.GetSQLSchema() + "].[__TR_" + table.DatabaseName + "__UPDATE]");
					sb.AppendLine("ON [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "]");
					sb.AppendLine("FOR UPDATE AS");
					sb.AppendLine("INSERT INTO [" + table.GetSQLSchema() + "].[__AUDIT__" + table.DatabaseName + "] ([__action]," + columnText + ")");
					sb.AppendLine("SELECT 2, " + columnValues + " FROM [inserted]");
					sb.AppendLine("GO");
					sb.AppendLine();
					sb.AppendLine("--CREATE TRIGGER DELETE FOR [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "]");
					sb.AppendLine("CREATE TRIGGER [" + table.GetSQLSchema() + "].[__TR_" + table.DatabaseName + "__DELETE]");
					sb.AppendLine("ON [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "]");
					sb.AppendLine("FOR DELETE AS");
					sb.AppendLine("INSERT INTO [" + table.GetSQLSchema() + "].[__AUDIT__" + table.DatabaseName + "] ([__action]," + columnText + ")");
					sb.AppendLine("SELECT 3, " + columnValues + " FROM [deleted]");
					sb.AppendLine("GO");
					sb.AppendLine();
				}

			}

			sb.AppendLine("--##SECTION END [AUDIT TRIGGERS]");
			sb.AppendLine();

		}
		#endregion
	}
}